package com.oracle.util;

import com.oracle.vo.TaskVo;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class TaskExcelUtil {

    /**
     * 导入excel
     * @param file
     * @param request
     * @return
     */
    public List<TaskVo> importCustomer(@RequestParam("file") MultipartFile file, HttpServletRequest request){

        //获得Servlet上下文引用，从上下文引用获得上传目录，真实目录地址
        String path = request.getSession().getServletContext().getRealPath("upload");
        //获得上传文件名字
        String fileName = file.getOriginalFilename();
        //根据上传文件目录和上传文件名创建上传文件File对象
        File targetFile = new File(path, fileName);
        //判断上传文件对象是否为空，如果为空，创建上传文件目录包含父目录
        if(!targetFile.exists()){
            targetFile.mkdirs();
        }

        List<TaskVo> orderinfoList = new ArrayList<TaskVo>();

        try {
            //把上传的文件保存到指定的目录
            file.transferTo(targetFile);
            //从制定目录读取文件
            FileInputStream filein=new FileInputStream(targetFile);
            //读取文件到WORKBOOK
            Workbook workbook= WorkbookFactory.create(filein);
            //读取第一张工作表
            Sheet sheet= workbook.getSheetAt(0);
            //判断工作表里面有几行内容
//            int rowNum = sheet.getPhysicalNumberOfRows();
            //判断工作表里面第一行有几个单元格（因为表格模板所有行的单元格数量一致）
//            int cellNum = sheet.getRow(0).getPhysicalNumberOfCells();

            // 对Sheet中的每一行进行迭代
            for (Row r : sheet) {
                // 如果当前行的行号（从0开始）未达到2（第三行）则从新循环
                if (r.getRowNum() < 1) {
                    continue;
                }
                // 创建实体类
                TaskVo info = new TaskVo();

                // 取出当前行第1个单元格数据，并封装在info实体stuName属性上
//                TaskVo.setOnum(r.getCell(0) == null ? "" : r.getCell(0)
//                        .getStringCellValue());
//                String eid_String = r.getCell(1).getStringCellValue();
//                info.setEid(StringUtils.isEmpty(eid_String)  ? 0 : Integer.valueOf(eid_String));
//
//                info.setPrice(r.getCell(2) == null ? null : new BigDecimal(r.getCell(2).getStringCellValue()));
//                info.setFromname(r.getCell(3) == null ? "" : r.getCell(3).getStringCellValue());
//                info.setFromaddress(r.getCell(4) == null ? "" : r.getCell(4)
//                        .getStringCellValue());
//                info.setFromtel(r.getCell(5) == null ? "" : r.getCell(5)
//                        .getStringCellValue());
//                info.setToname(r.getCell(6) == null ? "" : r.getCell(6)
//                        .getStringCellValue());
//                info.setToaddress(r.getCell(7) == null ? "" : r.getCell(7)
//                        .getStringCellValue());
//                info.setTotel(r.getCell(8) == null ? "" : r.getCell(8)
//                        .getStringCellValue());
//                info.setFromcardnum(r.getCell(9) == null ? "" : r.getCell(9)
//                        .getStringCellValue());
//                info.setState(r.getCell(10) == null ? 0 : Integer.valueOf(r.getCell(10)
//                        .getStringCellValue()));
//                info.setCreatetime(r.getCell(11) == null ? null : DateUtils.formatDate2(r.getCell(11)
//                        .getStringCellValue()));

                orderinfoList.add(info);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return orderinfoList;


    }

    /**
     * 导出
     * @param request
     * @param response
     * @param execlName
     * @param dataList
     * @throws Exception
     */
    public void exportExcel(HttpServletRequest request, HttpServletResponse response, String execlName, List dataList) throws Exception {

        response.setCharacterEncoding("utf-8");

       // 获取下载路劲
//        String path = request.getSession().getServletContext().getRealPath("/upload/");
        String path = "d:/upload/";
        //生成的PDF文档名称
        String fileName = execlName + ".xlsx";

        //易于扩展
        //如果文件名中包含order 调用创建orderexecl 的方法 例：orderexcel
        //创建Excel
        createTaskExcel(path + fileName, dataList);

        File file = new File(path + fileName); // 根据文件路径获得File文件

        response.setContentType("application/x-xls;charset=GBK");
        // 文件名
        response.setHeader("Content-Disposition",
                "attachment;filename=\"" + new String(fileName.getBytes(), "ISO8859-1") + "\"");

        response.setContentLength((int) file.length());
        byte[] buffer = new byte[4096];// 缓冲区
        BufferedOutputStream output = null;
        BufferedInputStream input = null;
        try {
            output = new BufferedOutputStream(response.getOutputStream());
            input = new BufferedInputStream(new FileInputStream(file));
            int n = -1;
            // 遍历，开始下载
            while ((n = input.read(buffer, 0, 4096)) > -1) {
                output.write(buffer, 0, n);
            }
            output.flush();
            response.flushBuffer();
        } catch (Exception e) {
            // 异常自己捕捉
        } finally {
            // 关闭流，不可少
            if (input != null)
                input.close();
            if (output != null)
                output.close();
        }
    }

    public void createTaskExcel(String dest, List<TaskVo> dataList) throws IOException {
        //1、创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2、新建工作表
        XSSFSheet sheet = workbook.createSheet("20240102");

        //--------------------------- 样式(选做) -------------------------------
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        //--------------------------- 样式(选做) -------------------------------

        packTaskData(sheet, style, dataList);

        //6、保存工作簿
        FileOutputStream fileout = new FileOutputStream(dest);
        workbook.write(fileout);
        System.out.println("创建2007格式的Excel成功");
    }

    private void packTaskData(XSSFSheet sheet, XSSFCellStyle style, List<TaskVo> datalist) {

        String[] headers = {"任务名称", "发起时间", "发起人", "最后审批时间", "状态"};
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //3、新增行
        for (int i = 0; i < datalist.size(); i++) {
            row = sheet.createRow(i + 1);

            row.createCell(0).setCellValue(datalist.get(i).getName());
            row.createCell(1).setCellValue(datalist.get(i).getStartTime());
            row.createCell(2).setCellValue(datalist.get(i).getTrueName());
            row.createCell(3).setCellValue(datalist.get(i).getEndTime());
            row.createCell(4).setCellValue(datalist.get(i).getTaskStatus());
        }
    }


}
